We built forecast models to set a useful baseline for the business to build from. The business owner for this metric will ultimately set where were they want the business goal to land.
For the new member model acquisition model we forecasted new members from center and new members from web separately as those two different channels are managed differently. We also observe they are were affected by external factors differently (ie COVID). Therefore it is not reasonable to combine these two data sets together when forecasting.
3 different forecasting models were applied, and are displayed below. These different models allow us to understand the potential impact of different scenarios.
| 2017 | 2018 | 2019 | 2020 | 2021 | 20221 | 2022FC_M1C | 2022FC_M2C | 2022FC_M3C | Business Goal | |
|---|---|---|---|---|---|---|---|---|---|---|
| Jan | 5,424 | 7,891 | 6,825 | 5,132 | 1,574 | 1,788 | 2,477 | 3,085 | 6,474 | TBD |
| Feb | 6,765 | 8,389 | 6,391 | 9,612 | 1,651 | - | 3,131 | 4,010 | 6,634 | TBD |
| Mar | 10,677 | 10,124 | 10,508 | 4,899 | 5,904 | - | 5,176 | 6,296 | 9,588 | TBD |
| Apr | 12,095 | 8,390 | 10,895 | 1 | 4,740 | - | 4,509 | 5,686 | 10,303 | TBD |
| May | 11,429 | 9,269 | 10,748 | 1,466 | 4,813 | - | 3,551 | 4,809 | 9,838 | TBD |
| Jun | 8,022 | 13,776 | 11,852 | 3,078 | 4,542 | - | 4,249 | 5,597 | 10,383 | TBD |
| Jul | 12,600 | 12,832 | 16,234 | 6,524 | 8,885 | - | 7,254 | 8,780 | 13,474 | TBD |
| Aug | 12,958 | 14,310 | 19,023 | 8,270 | 8,590 | - | 8,096 | 9,716 | 14,376 | TBD |
| Sep | 8,077 | 9,248 | 8,261 | 5,781 | 5,865 | - | 3,020 | 4,549 | 7,415 | TBD |
| Oct | 7,399 | 5,934 | 6,250 | 3,874 | 5,634 | - | 2,145 | 3,384 | 6,180 | TBD |
| Nov | 13,418 | 11,116 | 11,007 | 6,012 | 6,612 | - | 4,584 | 6,376 | 10,220 | TBD |
| Dec | 11,257 | 11,639 | 9,620 | 4,842 | 5,626 | - | 4,276 | 6,157 | 9,704 | TBD |
| total | 120,121 | 122,918 | 127,614 | 59,491 | 64,436 | — | 52,467 | 68,446 | 114,590 | — |
|
1
Note that 2022 actuals represent the current values and is not a complete month
|
||||||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 20221 | 2022FC_M1W | 2022FC_M2W | 2022FC_M3W | Business Goal | |
|---|---|---|---|---|---|---|---|---|---|---|
| Jan | 368 | 790 | 2,141 | 1,413 | 1,110 | 1,549 | 3,291 | 2,821 | 2,890 | TBD |
| Feb | 422 | 767 | 892 | 1,284 | 1,266 | - | 2,825 | 2,351 | 2,301 | TBD |
| Mar | 797 | 1,034 | 1,657 | 845 | 2,254 | - | 3,584 | 3,054 | 2,885 | TBD |
| Apr | 703 | 947 | 1,170 | 75 | 1,875 | - | 3,262 | 2,696 | 2,706 | TBD |
| May | 674 | 1,119 | 1,469 | 735 | 2,451 | - | 3,519 | 2,826 | 2,935 | TBD |
| Jun | 638 | 1,471 | 1,631 | 1,277 | 2,159 | - | 3,632 | 2,905 | 2,947 | TBD |
| Jul | 1,100 | 1,951 | 2,328 | 1,968 | 4,003 | - | 4,613 | 3,806 | 3,692 | TBD |
| Aug | 1,539 | 2,375 | 3,253 | 2,248 | 4,214 | - | 5,012 | 4,146 | 4,140 | TBD |
| Sep | 840 | 1,588 | 1,140 | 2,056 | 2,851 | - | 3,980 | 3,087 | 2,967 | TBD |
| Oct | 1,076 | 870 | 1,350 | 1,605 | 2,539 | - | 3,927 | 2,945 | 2,964 | TBD |
| Nov | 1,475 | 1,813 | 2,544 | 2,548 | 4,811 | - | 4,878 | 3,869 | 3,626 | TBD |
| Dec | 1,221 | 1,506 | 1,981 | 2,515 | 3,202 | - | 4,599 | 3,510 | 3,335 | TBD |
| total | 10,853 | 16,231 | 21,556 | 18,569 | 32,735 | — | 47,122 | 38,014 | 37,387 | — |
|
1
Note that 2022 actuals represent the current values and is not a complete month
|
||||||||||
For this analysis we have focused on analyzing the count of new members by day. The data is then split on whether the source of the membership was the Web or Center team (as identified by the “FromWeb” field in the database.
For forecasting purposes we have pulled data for all centers until now (2022.01.28). The forecast model excluded any data before 2017 as values were much lower.
Source: TangStats (server: appsql-prod.database.windows.net)
Tables: [dbo].[tblTransactionDetail]
We were pull instances where the product id was “1” (New TC Membership)
* Removing records where there were status wasn’t either “A” or “N”
* Removing records where there was a deleted timestamp.
The following centers were removed from the historical counts:
* “NE Prop/N Conway”, “McMinnville”, “Barstow”, “Boaz”, “Bourne”, “Branson”, “Bromont”, “Burlington”, “Casa Grande”, “Corporate”, “Dalton”, “Jeffersonville”, “Kittery”, “Lincoln City”, “Martinsburg”, “McMinnville”, “Nags Head”, “No Center Assigned”, “North Branch”, “Ocean City”, “Park City”, “Pigeon Forge”, “Saint Sauveur”, “Sanibel”, “Seymour”, “Stroud”, “Terrell”, “TEST CENTER”, “Tuscola”, “Vero Beach”, “West Branch”, “Westbrook”, “Williamsburg”, “Wisconsin Dells”
Before we begin to model this work, we want to visualize the general trends. This allows us to to flag for large outliers or anomalies within our data.
Each color line represents a different year’s worth of traffic aggregated on a monthly basis.
This method can account for the effects of confounders changing over time, by weighting the control group to better match the treatment group before the intervention. Another advantage of the synthetic control method is that it allows researchers to systematically select comparison groups. It has been applied to the fields of political science, health policy, criminology, and economics.
In our case, we are using this methodology to help set a baseline of where the business would be if we continue with the same historical processes and procedures as before.
Building model for all entire portfolio.
Traffc <-
CleanNewMem %>%
filter(Date < as.Date("2022-01-01")) %>%
mutate(ds = Date) %>%
group_by(ds) %>%
summarise(y = sum(NewMemCount, na.rm=T)) %>%
ungroup()
# m <- prophet(Traffc, holidays = covid)
m <- prophet(holidays = covid)
m <- add_country_holidays(m, country_name = 'US')
m <- fit.prophet(m, Traffc)
future <- make_future_dataframe(m, periods = 365)
# tail(future)
forecast <- predict(m, future)
# tail(forecast[c('ds', 'yhat', 'yhat_lower', 'yhat_upper')])Below is the forecast model.
We see a strong degree of seasonality
A forecast model can be broken up into the different elements that add (or multiply) together. By splitting them out and visualizing them, we then can view the index values and assess the impact of different items on the results
By breaking down the forecast into it’s component parts we see a couple of things:
Note about negative values: These 4 items below are added together to create the forecast model. This is why there are some items that are negative and some that are positive. A negative value does not mean that we would forecast negative vehicles, but instead that at those instances the traffic would decrease from the trend.
Below is the forecast model.
We see a strong degree of seasonality
The “Top Line” section presented both the actuals traffic by month for prior years with the forecasted values for 2022 (bolded).
The forecasted values could represent what would be considered as the baseline for goal setting by the business. Upon which the business leaders responsible for this metric would review and adjust the values according to where they believe they can made an impact. It will be up to the business to determine what strategies they will implement and adjust the goals accordingly.
This analysis body of work is set up to allow for the adjustment of goals based upon the values decided upon by the business owners.
With tc_new_members (TC_CustID, CreatedBy, CenterID,CenterName, FromWeb, TransactionDetailID, TransactionID, ProductID, ForCenterID,
Quantity, Price, Status, CreatedOn, ModifiedOn, TransactionDate) as
(
Select t.CustomerID, t.CreatedBy, t.CenterID,c.CenterName, t.FromWeb, td.TransactionDetailID, td.TransactionID, td.ProductID,
td.ForCenterID, td.Quantity, td.Price, td.Status, cast(td.CreatedOn as date), td.ModifiedOn, cast(t.TransactionDate as date)
From tblTransactionDetail td
Inner Join tblTransactions t
On td.TransactionID = t.TransactionID
left join tblcenters c ON t.centerid = c.centerid
Where td.ProductID = 1 --New TC Membership
And td.DeletedOn is null
And td.Status in ('A', 'N')
And t.Status in ('A', 'N')
)
select *
from tc_new_membersThe forecast model code is available within the I&A OneDrive.